DROP TABLE IF EXISTS ads.ads_trade_statics_2020;
create table if not exists ads.ads_trade_statics_2020(
ordercnt bigint, -- 订单总量
totalMoney decimal, --订单总额
quarters string, --季度
monthofyear string, --月份
dayofweek string, --星期
holiday string  /*工作日0 休息日1 法定假日2*/
) comment '2020 订单 时间 统计';

/*加载 */
/**统计2020年每个季度的销售订单笔数、订单总额 */
insert overwrite table ads.ads_trade_statics_2020
(
select count(orderid) ordercnt, sum(totalMoney) totalMoney,  string(quarters), '' as monthofyear, '' as dayofweek, '' as holiday
from dws.dws_trade_order_2020
group by quarters
union all
 /**统计2020年每个月的销售订单笔数、订单总额*/
select count(orderid), sum(totalMoney), '' as quarters,  string(monthofyear), '' as dayofweek, '' as holiday
from dws.dws_trade_order_2020
group by monthofyear
union all
/**统计2020年每周（周一到周日）的销售订单笔数、订单总额 */
select count(orderid), sum(totalMoney), '' as quarters, '' as monthofyear,  string(dayofweek), '' as holiday
from dws.dws_trade_order_2020
group by dayofweek
union all
/**  统计2020年国家法定节假日、休息日、工作日的订单笔数、订单总额 */
select count(orderid), sum(totalMoney), '' as quarters, '' as monthofyear, '' as dayofweek, 
case holiday when 0 then '工作日'
    when 1 then '休息日'
    else '法定假日'
end
from dws.dws_trade_order_2020
group by holiday);